package gov.pbc.nn.core.bdf.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.bstek.bdf2.core.CoreJdbcDao;
import com.bstek.bdf2.core.business.IDept;
import com.bstek.bdf2.core.model.DefaultDept;
import com.bstek.bdf2.core.service.IDeptService;

import gov.pbc.nn.core.bdf.entity.Dept;

public class DeptService extends CoreJdbcDao implements IDeptService {

	@Override
	public IDept loadDeptById(String deptId) {
		String sql = "SELECT D.* FROM T_DEPT D WHERE D.ID_=?";
		List<IDept> depts = this.getJdbcTemplate().query(sql, new Object[] { deptId }, new DefaultDeptRowMapper());
		if (depts.size() == 0) {
			return null;
		} else {
			return depts.get(0);
		}
	}

	@Override
	public List<IDept> loadDeptsByParentId(String parentId, String companyId) {
		String sql = "SELECT D.* FROM T_DEPT D WHERE D.COMPANY_ID_=?";
		if (parentId == null) {
			sql += " AND D.PARENT_ID_ IS NULL";
			sql += "  ORDER BY D.ID_";
			return this.getJdbcTemplate().query(sql, new Object[] { companyId }, new DefaultDeptRowMapper());
		} else {
			sql += " AND D.PARENT_ID_=?";
			sql += "  ORDER BY D.ID_";
			return this.getJdbcTemplate().query(sql, new Object[] { companyId, parentId }, new DefaultDeptRowMapper());
		}
	}

	@Override
	public List<IDept> loadUserDepts(String username) {
		String sql = "SELECT D.* FROM BDF2_USER_DEPT UD INNER JOIN T_DEPT D ON UD.DEPT_ID_=D.ID_ WHERE UD.USERNAME_=?";
		List<IDept> depts = this.getJdbcTemplate().query(sql, new Object[] { username }, new DefaultDeptRowMapper());
		for (IDept dept : depts) {
			this.buildParentDept(dept);
		}
		return depts;
	}

	@Override
	public IDept newDeptInstance(String deptId) {
		return new DefaultDept(deptId);
	}

	private void buildParentDept(IDept dept) {
		Dept d = (Dept) dept;
		if (d != null) {
			if (StringUtils.isNotEmpty(d.getParentId())) {
				IDept parentDept = this.loadDeptById(d.getParentId());
				d.setParent(parentDept);
				this.buildParentDept(parentDept);
			}
		}
	}

	public static class DefaultDeptRowMapper implements RowMapper<IDept> {
		public Dept mapRow(ResultSet rs, int rowNum) throws SQLException {
			Dept d = new Dept();
			d.setId(rs.getString("ID_"));
			d.setName(rs.getString("NAME_"));
			d.setDesc(rs.getString("DESC_"));
			d.setCompanyId(rs.getString("COMPANY_ID_"));
			d.setParentId(rs.getString("PARENT_ID_"));
			d.setDeptLevel(rs.getString("DEPT_LEVEL_"));
			return d;
		}
	}

}
